from stock_service import get_conn
from stock import East, baoStock
import logging


def init_genes():
    sql_template = "REPLACE INTO genes(code, market, name) " \
                   "values (?,?,?)"
    with get_conn() as conn:
        c = conn.cursor()
        for gene in East.all_genes_one_by_one():
            c.execute(sql_template, (gene['code'], gene['market']))
            logging.info('更新数据 -- %s' % gene)
        conn.commit()


def init_gene_kl(code, market):
    sql_template = "replace INTO genes_kl(code, date, open, close, low, " \
                   "high, total_volume, total_amount) " \
                   "values(?,?,?,?,?,?,?,?)"
    with get_conn() as conn:
        c = conn.cursor()
        try:
            for kl in East.load_gene_all_klines_one_by_one(code, market):
                c.execute(sql_template, (
                    kl['code'], kl['date'], kl['open'], kl['close'], kl['low'],
                    kl['high'], kl['total_volume'], kl['total_amount']
                ))
                logging.info('insert success %s' % kl)
                conn.commit()
        except Exception as e:
            logging.error('error =>', exc_info=e)


def init_gene_stocks(code):
    sql_template = "INSERT INTO gene_stocks(stock_code, gene_code) " \
                   "select ?,? where not exists(select 1 from gene_stocks where gene_code=? and stock_code=?)"
    with get_conn() as conn:
        c = conn.cursor()
        try:
            for stock in East.load_gene_stocks_one_by_one(code):
                c.execute(sql_template, (
                    stock['code'], code, code, stock['code']
                ))
                logging.info('insert %s %s' % ('success' if c.rowcount > 0 else 'failure', stock))
            conn.commit()
        except Exception as e:
            logging.error('error =>', exc_info=e)
            conn.rollback()


def init_stock_kls(code, market, start_date, end_date):
    sql_template = "INSERT INTO stock_kl(code, date, open, close, low, " \
                   "high, total_volume, total_amount) " \
                   "select ?,?,?,?,?,?,?,? where " \
                   "not exists(select id from genes_kl where code = ? and date = ?)"
    with get_conn() as conn:
        _code = ("sh." if market == 1 else "sz.") + code
        kls = baoStock.query_history_one_by_one(_code, start_date, end_date)
        c = conn.cursor()
        try:
            for kl in kls:
                c = c.execute(sql_template, (
                    code, kl[0], kl[1], kl[4], kl[3],
                    kl[2], kl[5], kl[6], code, kl[0]
                ))
                logging.info('insert success %s' % kl)
            conn.commit()
        except Exception as e:
            logging.error('insert error =>', exc_info=e)
            conn.rollback()


def init_stocks():
    sql_template = "REPLACE INTO stock(code, market, name) " \
                   "values (?,?,?)"
    with get_conn() as conn:
        c = conn.cursor()
        try:
            for stock in baoStock.query_industry_one_by_one():
                s_name = stock[2]
                s_market = 1 if stock[1].split('.')[0] == 'sh' else 0
                s_code = stock[1].split('.')[1]
                c.execute(sql_template, (s_code, s_market, s_name))
                logging.info('insert %s of %s.%s' % ('success' if c.rowcount > 0 else 'failure', s_market, s_code))
            conn.commit()
        except Exception as e:
            logging.error('insert error =>', exc_info=e)
            conn.rollback()


if __name__ == '__main__':
    logging.basicConfig(level=logging.INFO)
    # init_gene_kl('BK0547', 90)
    # init_gene_stocks('BK0547')
    # init_stocks()
    init_stock_kls("300810", 0, "2020-03-10", "2020-03-25")
